import xlrd
import time
import xlwt


class Const(object):
    """
    描述：全局配置
    """
    # 章节标题list
    functionPointLevel = ['一级功能', '二级功能', '三级功能', '四级功能', '五级功能', '六级功能', '七级功能', '八级功能']
    actualFunctionLevel = []
    testItem = ''  # 测试项编号，解决测试项合并问题
    # 功能级别对应的标题级别
    titleLevel = [2, 3, 4, 5, 6, 7, 8, 9]
    # Excel数据源文件名称（即：待转成word的excel数据）
    excelFileName = '测试点清单.xls'
    sheetName = '清单'
    # 用例表头
    caseTitle = ['用例编号', '用例名称', '功能点', '用例摘要', '用例设计者', '设计日期', '用例初始化', '输入数据', '测试步骤', '预期结果', '用例执行者', '执行日期', '实际结果', '测试结论', '备注', '复测截图']
    # 对指定列设定知否居中
    centerList = [True, True, True, False, True, True, False, False, False, False, True, True, False, True, True, False]
    # 设置指定列的列宽
    columnWidthList = [10, 15, 10, 20, 10, 10, 10, 10, 40, 20, 10, 10, 20, 10, 10, 10]
    # 输出结果文件的前缀名称
    exportNamePrefix = '用例结果'
    genType1 = '检查功能'
    genType2 = '接口测试'


class ExcelHelper(object):
    """
    描述：读取 Excel 表格数据的类
    """
    @staticmethod
    def read_by_list(excel_name, sheet_name):
        """
        作用：读取 Excel 表格中指定 sheet 的表格数据
        :param excel_name: Excel 文件名
        :param sheet_name: Sheet 名
        :return: 列表(sheet_data)
        """
        data_list = xlrd.open_workbook(excel_name)
        sheet = data_list.sheet_by_name(sheet_name)
        sheet_data = []
        for i in range(sheet.nrows):
            value = sheet.row_values(i)
            if type(value) == str:
                value = value.strip()
            sheet_data.append(value)
        return sheet_data

    @staticmethod
    def read_by_dict(excel_name, sheet_name):
        """
        作用：读取 Excel 表格中指定 sheet 的表格数据
        :param excel_name: Excel 文件名
        :param sheet_name: Sheet 名
        :return: 字典(dict_data)
        """
        st_data = ExcelHelper.read_by_list(excel_name, sheet_name)
        list_row1 = st_data[0]
        dict_data = []
        a = True
        for data in st_data:
            if a:
                a = False
                continue
            list_dict = dict(zip(list_row1, data))
            dict_data.append(list_dict)
        return dict_data


class XlwtUtils(object):
    """
        描述：将数据输出至 Excel 表格的封装类
    """
    @staticmethod
    def createBook():
        # 创建工作簿对象，设置编码格式为“utf-8”，默认格式是ASCII
        return xlwt.Workbook(encoding='utf-8')

    @staticmethod
    def createSheet(workBook, sheetName):
        # 通过工作薄对象创建sheet,sheetName 要求长度小于31个字符，并且不能存在斜杠，否则会报错
        return workBook.add_sheet(sheetName)

    @staticmethod
    def writeData(sheet, headerList, excelData, center):
        style = xlwt.XFStyle()
        XlwtUtils.borderStyle(style)
        # 写标题行的数据
        for i in range(len(headerList)):
            XlwtUtils.centerStyle(style, True)
            sheet.write(0, i, headerList[i], style)
        # 写数据行
        for i in range(len(excelData)):
            for j in range(len(headerList)):
                XlwtUtils.centerStyle(style, center[j])
                sheet.write(i + 1, j, excelData[i].get(headerList[j]), style)

    @staticmethod
    def saveBook(workBook, destinationPath):
        workBook.save(destinationPath)

    @staticmethod
    def setColumnWidth(sheet, headerList, headWidthList):
        for i in range(len(headerList)):
            sheet.col(i).width = 256 * headWidthList[i]

    @staticmethod
    def setCenter(sheet, headerList, centerList):
        for i in range(len(headerList)):
            sheet.col(i).width = 256 * centerList[i]

    @staticmethod
    def centerStyle(style, center):
        al = xlwt.Alignment()
        if center:
            al.horz = 0x02  # 设置水平居中
            al.vert = 0x01  # 设置垂直居中
        else:
            al.horz = 0x00  # 设置水平居中
            al.vert = 0x01  # 设置垂直居中
        al.wrap = 1
        style.alignment = al

    @staticmethod
    def borderStyle(style):
        borders = xlwt.Borders()
        # 设置边框属性实线、黑色
        borders.left = xlwt.Borders.THIN
        borders.top = xlwt.Borders.THIN
        borders.right = xlwt.Borders.THIN
        borders.bottom = xlwt.Borders.THIN
        style.borders = borders


class Generate(object):

    @staticmethod
    def transfer(excelDataList):
        # 解决标题
        currentDict = {}
        for item in Const.actualFunctionLevel:
            currentDict[item] = ''
        caseList = []
        i = 10
        for data1 in excelDataList:
            for item in Const.actualFunctionLevel:
                if data1.get(item) is not None and data1.get(item) != '' and data1.get(item) != '/':
                    currentDict[item] = data1.get(item)
            funcConnect = "-".join(Generate.append(currentDict))

            if data1.get('菜单路径') is not None and data1.get('菜单路径') != '':  # 如果系统菜单路径不为空，则以菜单路径作为用例操作路径
                funcConnect = data1.get('菜单路径')

            abstract = ''  # 如果用例摘要不为空，则拿用例摘要，如果不为空则用实际路径+测试点作为摘要
            if data1.get('用例摘要') is not None and data1.get('用例摘要') != '':
                abstract += data1.get('用例摘要')
            else:
                funcConnect + '：' + data1.get('测试点')

            testEnd = ''
            if data1.get('测试点').endswith("。"):
                testEnd += ''
            else:
                testEnd += '。'

            testStep = ''
            if data1.get('类型') == Const.genType2:
                testStep += '1、根据需求及接口文档；\r\n'
                testStep += '2、开发与调试“' + funcConnect + '”接口脚本；\r\n'
            else:
                testStep += '1、访问并登录系统；\r\n'
                testStep += '2、进入“' + funcConnect + '”；\r\n'
            testStep += '3、检查是否正确：' + data1.get('测试点') + testEnd

            caseDict = {}
            for item in Const.actualFunctionLevel:
                caseDict[item] = data1.get(item)

            if data1.get("测试项编号") is not None and data1.get('测试项编号') != '':  # 如果测试项编号单元格不为空，则说明要变测试项号了
                Const.testItem = data1.get("测试项编号")

            respectResult = ''
            if data1.get('预期结果') is not None and data1.get('预期结果') != '':
                respectResult += data1.get('预期结果')
            else:
                respectResult += '内容正确：' + data1.get('测试点')

            caseDict.update({
                Const.caseTitle[0]: Const.testItem + '-' + str(i).zfill(4),  # 用例编号
                Const.caseTitle[1]: funcConnect + '：' + data1.get('测试点'),  # 用例名称
                Const.caseTitle[2]: data1.get('功能点'),  # 功能点
                Const.caseTitle[3]: abstract,  # 用例摘要
                Const.caseTitle[4]: '黄某某',  # 用例设计者
                Const.caseTitle[5]: time.strftime('%Y.%m.%d', time.localtime(time.time())),  # 设计日期
                Const.caseTitle[6]: data1.get('前置操作'),  # 用例初始化
                Const.caseTitle[7]: data1.get('账号/角色'),  # 输入数据
                Const.caseTitle[8]: testStep,  # 测试步骤
                Const.caseTitle[9]: respectResult,  # 预期结果
                Const.caseTitle[10]: '黄某某',  # 用例执行者
                Const.caseTitle[11]: '',  # 执行日期
                Const.caseTitle[12]: respectResult + '。图示如下：\r\n$' + str(i).zfill(4) + '-01.png$',  # 实际结果
                Const.caseTitle[13]: '',  # 测试结论
                Const.caseTitle[14]: data1.get('备注'),  # 备注
                '复测截图': '复测结果：\r\n$' + str(i).zfill(4) + '-06.png$'
            })
            caseList.append(caseDict)
            i += 10
        return caseList

    @staticmethod
    def append(caseRow):
        """
        描述：用于将dict字典的功能名称转化成list
        :param caseRow:
        :return:
        """
        funcList = []
        for title in Const.functionPointLevel:
            titleName = caseRow.get(title)
            if titleName is not None and titleName != '' and titleName != '/':
                funcList.append(titleName)
        return funcList

    @staticmethod
    def actualLevel(excelDataList):
        """
        描述：获取当前excel表中实际的最大功能级别
        :param excelDataList:
        :return:
        """
        maxLevel = ''
        for item in excelDataList:
            for level in Const.functionPointLevel:
                if item.get(level) is not None and item.get(level) != '' and item.get(level) != '/':
                    maxLevel = level
        for item in Const.functionPointLevel:
            Const.actualFunctionLevel.append(item)
            if item == maxLevel:
                break


if __name__ == '__main__':
    print('程序开始运行...')
    # 步骤一：读取Excel表格数据、Excel表格的表头
    excelData = ExcelHelper.read_by_dict(Const.excelFileName, Const.sheetName)
    # 获取测试点excel表中最大的功能级别
    Generate.actualLevel(excelData)
    # 步骤二：生成用例
    resultList = Generate.transfer(excelData)
    # 步骤三：将转换结果输出
    book = XlwtUtils.createBook()
    newSheet = XlwtUtils.createSheet(book, "测试用例")
    XlwtUtils.writeData(newSheet, Const.caseTitle, resultList, Const.centerList)
    XlwtUtils.setColumnWidth(newSheet, Const.caseTitle, Const.columnWidthList)  # 设置居中
    fileName = '生成结果-' + time.strftime('%Y%m%d-%H%M', time.localtime(time.time())) + '.xlsx'
    XlwtUtils.saveBook(book, fileName)
    print('运行结束...请查看结果文件：' + fileName)

